<cfcomponent output="false">

	<cffunction name="get" output="false" access="remote">
		<cfargument name="workshopID" required="true" />
		<cfargument name="organizationID" type="numeric" required="true">
 		<cfreturn createObject("component", "Workshop").init(arguments.workshopID, arguments.organizationID)>
	</cffunction>

	<cffunction name="save" output="false" access="remote">
		<cfargument name="obj" required="true" />
 		<cfreturn obj.save() />
	</cffunction>

	<cffunction name="delete" output="false" access="remote">
		<cfargument name="workshopID" required="true" />
		<cfargument name="organizationID" required="true" />
		<cfset var qDelete = "">
		<cfquery name="qDelete" datasource="#application.datasource#">
			update Workshop
			set Deleted = 1
			where Workshop_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.workshopID#">
			and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
		</cfquery>
	</cffunction>
	
	<cffunction name="approve" output="false" access="remote">
		<cfargument name="workshopID" type="numeric" required="true" />
		<cfargument name="organizationID" type="numeric" required="true">
		<cfset var qApprove = "">
		<cfquery name="qApprove" datasource="#application.datasource#">
			update Workshop
			set Approved = 1
			where Workshop_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.workshopID#">
			and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
		</cfquery>
	</cffunction>

	<cffunction name="getAll" output="false" access="remote">
		<cfargument name="organizationID" type="numeric" required="true">
		<cfset var qRead="">
		<cfset var obj="">
		<cfset var ret=arrayNew(1)>

		<cfquery name="qRead" datasource="#application.datasource#">
			select Workshop_ID
			from Workshop
			where Deleted = 0
			<cfif arguments.organizationID gt 0>
				and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
			</cfif>
			order by Workshop_Date
		</cfquery>

		<cfloop query="qRead">
		<cfscript>
			obj = createObject("component", "Workshop").init(qRead.Workshop_ID, arguments.organizationID);
			ArrayAppend(ret, obj);
		</cfscript>
		</cfloop>
		<cfreturn ret>
	</cffunction>

	<cffunction name="getAllAsQuery" output="false" access="remote" returntype="query">
		<cfargument name="fieldlist" default="*" hint="List of columns to be returned in the query.">
		<cfargument name="organizationID" type="numeric" required="no" default="0">
		<cfargument name="regionID" type="numeric" required="no" default="0">
		<cfargument name="orderBy" type="string" required="no" default="Workshop_Date">
		<cfargument name="orderDir" type="string" required="no" default="asc">
		<cfargument name="organizationIDs" type="string" required="no" default="All">
		<cfargument name="regionIDs" type="string" required="no" default="All">
		<!--- <cfargument name="pbs" type="string" required="no" default="#application.DEFAULT_PBS#">
		<cfargument name="period" type="string" required="no" default="Future"> --->
		<cfargument name="approved" type="string" required="no" default="All">
		<cfargument name="workshopIDs" type="string" required="no" default="0">
		<cfargument name="computerBased" type="string" required="no" default="All">
		<cfargument name="programYear" type="string" required="no" default="#application.currentProgramYear#">
		<!--- <cfargument name="year" type="string" required="No" default="#application.utility.getCurrentProgramYear("workshop")#"> --->
		
		<cfset var qRead="">

		<cfquery name="qRead" datasource="#application.datasource#">
			select 	#arguments.fieldList#,
					Workshop.Approved as workshopApproved,
					Workshop.Evaluation_Code as exitSurveyCode,
					Workshop.Date_Created as workshopDateCreated,
					Organization.Approved as organizationApproved,
					Organizer.Approved as organizerApproved,
					Organizer.First_Name as organizerFirstName,
					Organizer.Last_Name as organizerLastName,
					Organizer.Phone as organizerPhone,
					<!--- Organizer.Extension as organizerExtension, --->
					Organizer.Email as organizerEmail,
					Site.Address as siteAddress,
					Site.City as siteCity,
					Site.Zip_Code as siteZipCode,
					Manager.Approved as siteContactApproved,
					Manager.First_Name as siteContactFirstName, 
					Manager.Last_Name as siteContactLastName,
					Manager.Phone as siteContactPhone,
					<!--- Manager.Extension as siteContactExtension, --->
					Manager.Email as siteContactEmail<!--- ,
(select count(*) from student where student.Workshop_ID = workshop.Workshop_ID and student.Deleted = 0 and Origin = 'Web') as wbStudentCount,
pbStudentCount	=	CASE (select count(*) from student where student.Workshop_ID = workshop.Workshop_ID and student.Deleted = 0 and Origin = 'Paper')
WHEN 0 THEN PB_SURVEY_COUNT
ELSE (select count(*) from student where student.Workshop_ID = workshop.Workshop_ID and student.Deleted = 0 and Origin = 'Paper')
END --->
			FROM	County 
			INNER JOIN Site ON County.County_ID = Site.County_ID 
			RIGHT OUTER JOIN Workshop 
			LEFT OUTER JOIN	Organization ON Workshop.Organization_ID = Organization.Organization_ID 
			LEFT OUTER JOIN	[User] Organizer ON Workshop.Organizer_ID = Organizer.User_ID 
			ON Site.Site_ID = Workshop.Site_ID 
			LEFT OUTER JOIN	[User] Manager ON Workshop.Manager_ID = Manager.User_ID  
			where Workshop.Deleted = 0
			<cfif arguments.workshopIDs neq 0>
				and Workshop.Workshop_ID in (<cfqueryparam list="yes" value="#arguments.workshopIDs#">)
			<cfelse>
				<cfif arguments.organizationID gt 0>
					and Workshop.Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
				<cfelseif arguments.organizationIDs neq "All">
					and Workshop.Organization_ID IN (<cfqueryparam value="#arguments.organizationIDs#" separator="," list="yes">)
				</cfif>
				<cfif arguments.regionID gt 0>
					and County.Region_ID = <cfqueryparam value="#arguments.regionID#" cfsqltype="cf_sql_integer">
				<cfelseif arguments.regionIDs neq "All">
					and County.Region_ID IN (<cfqueryparam list="yes" value="#arguments.regionIDs#">)
				</cfif>
				<!--- <cfif arguments.pbs eq "No">
					and County.Study_Region = 0
				<cfelseif arguments.pbs eq "Yes">
					and County.Study_Region = 1
				</cfif> --->
				<cfif arguments.programYear neq "All">
					and Workshop.Program_Year = '#arguments.programYear#'
				</cfif>
				<!--- <cfif arguments.period neq "All">
					<cfif arguments.period neq "Year">
						<cfif ListLen(arguments.period) eq 1>
							<cfif arguments.period eq "Future">
								and Workshop.Workshop_Date >= <cfqueryparam value="#DateFormat(now())#" cfsqltype="cf_sql_date">
							<cfelseif arguments.period eq "Past">
								and Workshop.Workshop_Date < <cfqueryparam value="#DateFormat(now())#" cfsqltype="cf_sql_date">
							<cfelseif arguments.period eq "Today">
								and Workshop.Workshop_Date < <cfqueryparam value="#DateFormat(DateAdd('d', 1, now()), 'm/d/yyyy')#" cfsqltype="cf_sql_date">
								and Workshop.Workshop_Date > <cfqueryparam value="#DateFormat(now(), 'm/d/yyyy')#" cfsqltype="cf_sql_date">
							</cfif>
						<cfelseif ListLen(arguments.period) eq 2>
							<cfif ListFirst(arguments.period) gt 0>
								and Workshop.Workshop_Date >= <cfqueryparam value="#DateFormat(now())#" cfsqltype="cf_sql_date">
								and Workshop.Workshop_Date <= <cfqueryparam value="#DateFormat(DateAdd(ListLast(arguments.period), ListFirst(arguments.period), now()))#" cfsqltype="cf_sql_date">
							<cfelseif ListFirst(arguments.period) lt 0>
								and Workshop.Workshop_Date < <cfqueryparam value="#DateFormat(now())#" cfsqltype="cf_sql_date">
								and Workshop.Workshop_Date >= <cfqueryparam value="#DateFormat(DateAdd(ListLast(arguments.period), ListFirst(arguments.period), now()))#" cfsqltype="cf_sql_date">
							</cfif>
						</cfif>
					<cfelse>
						<cfif structKeyExists(request.programYears.workshop, arguments.year)>
							and Workshop_Date >= <cfqueryparam value="#listFirst(request.programYears.workshop[arguments.year])#" cfsqltype="cf_sql_date">
							and Workshop_Date < <cfqueryparam value="#listLast(request.programYears.workshop[arguments.year])#" cfsqltype="cf_sql_date">
						<cfelse>
							and 1 = 0
						</cfif>
					</cfif>
				</cfif> --->
				<cfif arguments.approved eq "No">
					and Workshop.Approved = 0
				<cfelseif arguments.approved eq "Yes">
					and Workshop.Approved = 1
				</cfif>
				<cfif arguments.computerBased eq "No">
					and Workshop.Online = 0
				<cfelseif arguments.computerBased eq "Yes">
					and Workshop.Online = 1
				</cfif>
			</cfif>
			order by #arguments.orderBy# #arguments.orderDir#
		</cfquery>

		<cfreturn qRead>
	</cffunction>
	
	<cffunction name="getWorkshopByEvaluationCode" output="false" access="remote">
		<cfargument name="evaluationCode" required="true" />
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#" maxrows="1">
			select Workshop_ID, Organization_ID
			from Workshop
			where Evaluation_Code = <cfqueryparam value="#arguments.evaluationCode#" cfsqltype="cf_sql_varchar">
			and Approved = 1
			and Deleted = 0
		</cfquery>
		<cfif qRead.RecordCount eq 1>
			<cfreturn createObject("component", "Workshop").init(qRead.Workshop_ID, qRead.Organization_ID)>
		<cfelse>
			<cfreturn createObject("component", "Workshop").init(0, 0)>
		</cfif>
	</cffunction>
	
	<cffunction name="generateEvaluationCode" output="false" access="public" returntype="string">
		<!--- do unique check --->
		<cfset var ret = "">
		<cfset var alphaList = "a,b,c,d,e,f,g,h,i,j,k,m,n,p,q,r,s,t,u,v,w,x,y,z,2,3,4,5,6,7,8,9">
		<cfset ret = ListGetAt(alphaList, RandRange(1, 32)) & ListGetAt(alphaList, RandRange(1, 32)) & ListGetAt(alphaList, RandRange(1, 32)) & ListGetAt(alphaList, RandRange(1, 32)) & ListGetAt(alphaList, RandRange(1, 32))>
		<cfreturn ret>
	</cffunction>
	
	<cffunction name="submitPBTrackingData" output="true" access="remote" returntype="boolean">
		<cfargument name="workshopID" type="numeric" required="yes">
		<cfargument name="pbSurveyCount" type="numeric" required="yes">
		<cfargument name="pbSurveyCountStatus" required="yes">
		<cfargument name="pbShippedDate" type="string" required="no" default="">
		<cfargument name="pbNotes" type="string" required="no" default="">
		<cfset var qUpdate = "">
		<cfset var ret = "">
		<cftry>
			<cfquery name="qUpdate" datasource="#application.datasource#">
				update Workshop
				set PB_Survey_Count = <cfqueryparam value="#arguments.pbSurveyCount#" cfsqltype="cf_sql_integer">,
				PB_Survey_Count_Status = <cfqueryparam value="#arguments.pbSurveyCountStatus#" cfsqltype="cf_sql_varchar">,
				<cfif IsDate(arguments.pbShippedDate)>
					PB_Shipped_Date = <cfqueryparam value="#DateFormat(arguments.pbShippedDate)#" cfsqltype="cf_sql_date">,
				<cfelse>
					PB_Shipped_Date = Null,
				</cfif>
				PB_Notes = <cfqueryparam value="#arguments.pbNotes#" cfsqltype="cf_sql_varchar" null="#iif((arguments.pbNotes eq ""), de("yes"), de("no"))#">
				where Workshop_ID = <cfqueryparam value="#arguments.workshopID#" cfsqltype="cf_sql_integer">
			</cfquery>
			<cfset ret = true>
			<cfcatch type="any">
				<cfset ret = false>
			</cfcatch>
		</cftry>
		<cfreturn ret>
	</cffunction>
	
	<cffunction name="getApprovedWorkshopIDsByOrganizationID" output="false" access="remote" returntype="string">
		<cfargument name="organizationID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Workshop_ID
			from Workshop
			where Deleted = 0
			and Approved = 1
			and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
			and Workshop_Date >= <cfqueryparam value="#listFirst(application.utility.getCurrentProgramYear("fund"))#" cfsqltype="cf_sql_date">
			and Workshop_Date < <cfqueryparam value="#listLast(application.utility.getCurrentProgramYear("fund"))#" cfsqltype="cf_sql_date">
		</cfquery>
		<cfreturn ValueList(qRead.Workshop_ID)>
	</cffunction>
	
	<cffunction name="getCurrentWorkshopCountByOrganizationID" output="false" access="remote" returntype="string">
		<cfargument name="organizationID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select count(*) as recs
			from Workshop
			where Deleted = 0
			<cfif arguments.organizationID neq 0>
				and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
			</cfif>
			and Workshop_Date >= <cfqueryparam value="#listFirst(application.utility.getCurrentProgramYear("fund"))#" cfsqltype="cf_sql_date">
			and Workshop_Date < <cfqueryparam value="#listLast(application.utility.getCurrentProgramYear("fund"))#" cfsqltype="cf_sql_date">
		</cfquery>
		<cfreturn qRead.recs>
	</cffunction>
	
	<cffunction name="countByOrganizationID" output="false" access="remote" returntype="numeric">
		<cfargument name="organizationID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Count(*) as recs
			from Workshop
			where Deleted = 0
			and Organization_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizationID#">
		</cfquery>
		<cfreturn qRead.recs>
	</cffunction>
	
	<cffunction name="countByOrganizerID" output="false" access="remote" returntype="numeric">
		<cfargument name="organizerID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Count(*) as recs
			from Workshop
			where Deleted = 0
			and Organizer_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.organizerID#">
		</cfquery>
		<cfreturn qRead.recs>
	</cffunction>
	
	<cffunction name="countBySiteID" output="false" access="remote" returntype="numeric">
		<cfargument name="siteID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Count(*) as recs
			from Workshop
			where Deleted = 0
			and Site_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.siteID#">
		</cfquery>
		<cfreturn qRead.recs>
	</cffunction>
	
	<cffunction name="countByManagerID" output="false" access="remote" returntype="numeric">
		<cfargument name="managerID" type="numeric" required="yes">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Count(*) as recs
			from Workshop
			where Deleted = 0
			and Manager_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.managerID#">
		</cfquery>
		<cfreturn qRead.recs>
	</cffunction>
	
	<cffunction name="getNextWorkshop" output="false" access="remote">
		<cfargument name="organizationID" type="numeric">
		<cfset var qRead = "">
		<cfquery name="qRead" datasource="#application.datasource#">
			select Min(Workshop_Date) as nextWorkshopDate
			from Workshop
			where Organization_ID = <cfqueryparam value="#arguments.organizationID#" cfsqltype="cf_sql_integer">
 			and Workshop_Date >= getdate()
 			and Deleted = 0
		</cfquery>
		<cfreturn qRead.nextWorkshopDate>
	</cffunction>
	
	<cffunction name="searchWorkshops" output="false" access="remote" returntype="query">
		<cfargument name="county" type="string" required="no" default="">
		<cfargument name="city" type="string" required="no" default="">
		<cfargument name="zipCode" type="string" required="no" default="">
		<cfargument name="zipCodeRadiusInMiles" type="numeric" required="no" default="50">
		<cfargument name="orderBy" type="string" required="no" default="">
		<cfargument name="maxRows" type="numeric" required="no" default="100">
		<cfset var GetZipCodeLongLat = "">
		<cfset var qSearch = "">
		<cfif Len(Trim(arguments.zipCode)) gt 0>
			<cfquery name="GetZipCodeLongLat" datasource="wms" maxrows="1">
				select longitude, latitude
				from Zip_Code
				where Zip_Code = <cfqueryparam value="#arguments.zipCode#" cfsqltype="cf_sql_numeric">
			</cfquery>
		</cfif>
		<cfquery name="qSearch" datasource="wms" maxrows="#arguments.maxRows#">
			SELECT	Workshop.Workshop_Date, 
					Workshop.Start_Time,
					Workshop.End_Time, 
					Site.Site, 
					Site.Address, 
					Site.City, 
					Site.Zip_Code, 
					County.County, 
					Zip_Code.Latitude, 
					Zip_Code.Longitude, 
					Manager.First_Name, 
					Manager.Last_Name, 
					Manager.Email, 
					Manager.Phone, 
					Manager.Extension
					<cfif Len(Trim(arguments.zipCode)) gt 0 and GetZipCodeLongLat.RecordCount gt 0>
						,(SQRT(69.1 * (latitude - #GetZipCodeLongLat.latitude#) * 69.1 * (latitude - #GetZipCodeLongLat.latitude#) + 53 * (longitude - #GetZipCodeLongLat.longitude#) * 53 * (longitude - #GetZipCodeLongLat.longitude#))) as distance
					</cfif>
			<!--- FROM County 
			INNER JOIN Site ON County.County_ID = Site.County_ID 
			INNER JOIN Zip_Code ON Site.Zip_Code = Zip_Code.Zip_Code 
			RIGHT OUTER JOIN Workshop ON Site.Site_ID = Workshop.Site_ID 
			LEFT OUTER JOIN [User] Manager ON Workshop.Manager_ID = Manager.User_ID --->
			FROM    dbo.County RIGHT OUTER JOIN
					dbo.Site ON dbo.County.County_ID = dbo.Site.County_ID LEFT OUTER JOIN
					dbo.Zip_Code ON dbo.Site.Zip_Code = dbo.Zip_Code.Zip_Code RIGHT OUTER JOIN
					dbo.Workshop ON dbo.Site.Site_ID = dbo.Workshop.Site_ID LEFT OUTER JOIN
					dbo.[User] AS Manager ON dbo.Workshop.Manager_ID = Manager.User_ID
			WHERE	Workshop.Approved = 1
			AND 	Workshop.Deleted = 0
			AND 	Workshop.Workshop_Date >= #Now()#
			<cfif Len(Trim(arguments.zipCode)) gt 0 and GetZipCodeLongLat.RecordCount gt 0>
				AND	(SQRT(69.1 * (latitude - #GetZipCodeLongLat.latitude#) * 69.1 * (latitude - #GetZipCodeLongLat.latitude#) + 53 * (longitude - #GetZipCodeLongLat.longitude#) * 53 * (longitude - #GetZipCodeLongLat.longitude#)) <= #arguments.zipCodeRadiusInMiles#)
				<cfif Len(Trim(arguments.orderBy)) gt 0>
					ORDER By #arguments.orderBy#
				<cfelse>
					ORDER By distance
				</cfif>
			<cfelseif Len(Trim(arguments.city)) gt 0>
				AND City = <cfqueryparam value="#arguments.city#" cfsqltype="cf_sql_varchar">
				<cfif Len(Trim(arguments.orderBy))>
					ORDER By #arguments.orderBy#
				<cfelse>
					ORDER By Workshop.Workshop_Date
				</cfif>
			<cfelseif Len(Trim(arguments.county)) gt 0>
				AND County = <cfqueryparam value="#arguments.county#" cfsqltype="cf_sql_varchar">
				<cfif Len(Trim(arguments.orderBy))>
					ORDER By #arguments.orderBy#
				<cfelse>
					ORDER BY Workshop.Workshop_Date
				</cfif>
			<cfelse>
				AND 1 = 0
			</cfif>
		</cfquery>
		<cfreturn qSearch>
	</cffunction>
	
	<cffunction name="getWorkshopRegionID" output="no" access="public" returntype="numeric">
		<cfargument name="workshopID" type="numeric" required="yes">
		<cfquery name="GetWorkshopSiteID" datasource="#application.datasource#">
			select Site_ID 
			from Workshop
			where Workshop_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.workshopID#">
		</cfquery>
		<cfquery name="GetSiteCountyID" datasource="#application.datasource#">
			select County_ID 
			from Site
			where Site_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#GetWorkshopSiteID.Site_ID#">
		</cfquery>
		<cfquery name="GetSiteRegionID" datasource="#application.datasource#">
			select Region_ID
			from County
			where County_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#GetSiteCountyID.County_ID#">
		</cfquery>
		<cfreturn GetSiteRegionID.Region_ID>
	</cffunction>
</cfcomponent>